package com.construction.engineering.accounting.service.excel.impl;

import com.construction.engineering.accounting.common.result.BaseResult;
import com.construction.engineering.accounting.common.until.ExcelUntil;
import com.construction.engineering.accounting.common.until.MapUtil;
import com.construction.engineering.accounting.entity.excel.ConversionExcel;
import com.construction.engineering.accounting.service.excel.ConversionExcelToDllService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.io.BufferedWriter;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

@Slf4j
@Service
public class ConversionExcelToDllServiceImpl implements ConversionExcelToDllService {

    @Value("${file.upload.path}")
    private String filePath;


    @Override
    public BaseResult<String> conversionExcelToDll(String fileName) {

        String file_path_name = filePath + fileName;
        try (FileInputStream fis = new FileInputStream(file_path_name)) {
            Map<String, Map<String, String>> tableNames = new HashMap<>(); //sheet-表命-中文名
            Workbook workbook = ExcelUntil.getWorkbook(file_path_name, fis);
            // 遍历所有 Sheet
            for (Sheet sheet : workbook) {
                Map<String, String> table_name = new HashMap<>();
                String sheetName = sheet.getSheetName();
                for (Row row : sheet) {
                    if (row.getRowNum() == 0) {
                        continue;
                    }
                    String title_en = ExcelUntil.readCellValueAs(sheet, row.getRowNum(), 2, String.class);  //表名
                    String title_chi = ExcelUntil.readCellValueAs(sheet, row.getRowNum(), 3, String.class); //中文名
                    table_name.put(title_en, title_chi);
                }
                tableNames.put(sheetName, table_name);//记录所有的要处理的表名
            }

            Map<String, List<ConversionExcel>> conversion_map = new HashMap<>();

            for (Map.Entry<String, Map<String, String>> entry : tableNames.entrySet()) {
                String sheetName = entry.getKey();
                Map<String, String> tableNameValue = entry.getValue();
                String sheet_file_path = filePath + sheetName + ".xlsx";
                try (FileInputStream sheet_fis = new FileInputStream(sheet_file_path)) {
                    Workbook workbook_sheet = ExcelUntil.getWorkbook(sheet_file_path, sheet_fis);
                    Sheet sheet_sheet = workbook_sheet.getSheetAt(0);//只获取第一个sheet

                    Map<String, Integer> head_map = ExcelUntil.getHeadMap(sheet_sheet);
                    for (Row row : sheet_sheet) {
                        if (row.getRowNum() == 0) {
                            continue;
                        }
                        ConversionExcel conversion_excel = handleConversion(row, head_map, tableNameValue);
                        if (conversion_excel != null) {
                            if (conversion_map.get(conversion_excel.getTableName()) == null) {
                                List<ConversionExcel> list = new ArrayList<>();
                                list.add(conversion_excel);
                                conversion_map.put(conversion_excel.getTableName(), list);
                            } else {
                                List<ConversionExcel> list = conversion_map.get(conversion_excel.getTableName());
                                list.add(conversion_excel);
                                conversion_map.put(conversion_excel.getTableName(), list);
                            }
                        }
                    }
                } catch (Exception e) {
                    // 捕获异常并打印错误信息
                    System.err.println("Error parsing JSON: " + e.getMessage());
                }
                String txt_FileName = filePath  + sheetName + ".txt";
                File file = new File(txt_FileName);
                BufferedWriter w = new BufferedWriter(new FileWriter(file));

                for (Map.Entry<String, List<ConversionExcel>> conversion_entry : conversion_map.entrySet()) {
                    String tableName = conversion_entry.getKey();
                    List<ConversionExcel> list = conversion_entry.getValue();
                    createFile(w, tableName, list, sheetName);
                    w.write("\n");
                    w.write("\n");
                    w.write("\n");
                    w.write("\n");
                    w.write("\n");
                    w.write("\n");

                }
                w.flush();
                w.close();
            }
        } catch (Exception e) {
            e.printStackTrace();
            log.error("表字段转建表语句出现异常：{}", e.getMessage());
        }

        return null;
    }


    private ConversionExcel handleConversion(Row row, Map<String, Integer> head_map, Map<String, String> tableNameValue) {
        ConversionExcel conversion_excel = null;
        try {
            String tableName = null;
            String tableDep = null;
            String colName = null;
            String colType = null;
            String isNull = null;
            String colDep = null;
            String primaryKey = null;
            String onlyKey = null;
            String outKey = null;
            String index = null;
            if (head_map.get("表名") != null) {
                tableName = ExcelUntil.readStringCell(row,head_map.get("表名"));
            }
            if (head_map.get("表注释") != null) {
                tableDep = ExcelUntil.readStringCell(row,head_map.get("表注释"));
            }
            if (head_map.get("列名") != null) {
                colName = ExcelUntil.readStringCell(row,head_map.get("列名"));
            }
            if (head_map.get("字段类型") != null) {
                colType = ExcelUntil.readStringCell(row,head_map.get("字段类型"));
            }
            if (head_map.get("是否非空") != null) {
                isNull = ExcelUntil.readStringCell(row,head_map.get("是否非空"));
            }
            if (head_map.get("列注释") != null) {
                colDep = ExcelUntil.readStringCell(row,head_map.get("列注释"));
            }
            if (head_map.get("主键约束") != null) {
                primaryKey = ExcelUntil.readStringCell(row,head_map.get("主键约束"));
            }
            if (head_map.get("唯一约束") != null) {
                onlyKey = ExcelUntil.readStringCell(row,head_map.get("唯一约束"));
            }
            if (head_map.get("外键约束") != null) {
                outKey = ExcelUntil.readStringCell(row,head_map.get("外键约束"));
            }
            if (head_map.get("索引字段") != null) {
                index = ExcelUntil.readStringCell(row,head_map.get("索引字段"));
            }
            //数据在tableNameValue中表示需要处理这行数据
            if (StringUtils.isNotBlank(tableName) && tableNameValue != null && MapUtil.getValueIgnoreCase(tableNameValue, tableName) != null) {
                conversion_excel = new ConversionExcel();
                conversion_excel.setTableName(tableName);
                conversion_excel.setTableDep(tableDep);
                conversion_excel.setColName(colName);
                conversion_excel.setColType(colType);
                conversion_excel.setIsNull(isNull);
                conversion_excel.setColDep(colDep);
                conversion_excel.setPrimaryKey(primaryKey);
                conversion_excel.setOnlyKey(onlyKey);
                conversion_excel.setOutKey(outKey);
                conversion_excel.setIndex(index);
            }

        } catch (Exception e) {
            log.error("处理 handleConversion 异常：{}", e.getMessage());
        }
        return conversion_excel;
    }


    private void createFile(BufferedWriter w, String tableName, List<ConversionExcel> list, String sheetName) {
        try {
            String tableDep = null;
            StringBuilder key_line = new StringBuilder();

            w.write("CREATE TABLE " + "`" + tableName + "`" + " ( \n");

            String colName = "";
            boolean flag = false;

            Iterator<ConversionExcel> iterator = list.iterator();
            while (iterator.hasNext()) {
                ConversionExcel co = iterator.next();
                tableDep = co.getTableDep();
                boolean isLast = !iterator.hasNext(); // 如果没有下一个元素，就是最后一个
                if (isLast) {
                    if(flag){
                        w.write(splicing(co, true) + ",\n");
                    } else {
                        w.write(splicing(co, true) + "\n");
                    }
                } else {
                    w.write(splicing(co, false) + "\n");
                }

                if (StringUtils.isNotBlank(co.getPrimaryKey())  && "Y".equals(co.getPrimaryKey())) {
                    String primaryKey = "PRIMARY KEY (`" + co.getColName() + "`)";
                    key_line.append(primaryKey).append(",").append("\n");
                    if(co.getColType().contains("NUMERIC") || co.getColType().contains("numeric")){
                        colName = co.getColName();
                    }
                    flag = true;
                }
                if (StringUtils.isNotBlank(co.getOnlyKey()) && "Y".equals(co.getOnlyKey())) {
                    String onlyKey = "UNIQUE KEY `uk_" + tableName + "_" + co.getColName() + "` (`" + co.getColName() + "`)";
                    key_line.append(onlyKey).append(",").append("\n");
                    flag = true;
                }
                if ((StringUtils.isNotBlank(co.getIndex()) && "Y".equals(co.getIndex())) && (StringUtils.isBlank(co.getPrimaryKey())  || !"Y".equals(co.getPrimaryKey()))) {
                    String index = "KEY `INDEX_" + co.getColName() + "` (`" +co.getColName() + "`) USING BTREE";
                    key_line.append(index).append(",").append("\n");
                    flag = true;
                }
            }

            String line =  key_line.toString();
            if (line.endsWith(",\n")) {
                line = key_line.substring(0, line.length() - 2) + "\n";
            }
            w.write(line);

            String end = ") ENGINE=InnoDB DEFAULT CHARSET=utf8 ";
            if(StringUtils.isNotBlank(tableDep)){
                end = end + "COMMENT='"+ tableDep.replace("=",":")+"'";
            }
            w.write(end + ";");

            if(StringUtils.isNotBlank(colName)){
                w.write("\n");
                w.write("\n");

                w.write("ALTER TABLE `" + tableName + "` MODIFY COLUMN `" + colName +"` INT NOT NULL AUTO_INCREMENT;");

                w.write("\n");
                w.write("\n");
            }


        } catch (Exception e) {
            throw new RuntimeException("生成sql语句文件异常");
        }
    }


    private String splicing(ConversionExcel co, Boolean flag) {
        String colType = typeMapping(co.getColType());
        //先转换字段
        String isNull = "";
        if (co.getIsNull() != null && ("t".equals(co.getIsNull()) || "Y".equals(co.getIsNull()))) {
            isNull = "NOT NULL";
        } else {
            isNull = "NULL";
        }



        String comment = "";
        if (StringUtils.isNotBlank(co.getColDep())) {
            comment = "COMMENT '" + co.getColDep().replace("=", ":") + "'";
        }

        StringBuilder sb = new StringBuilder();
        sb.append("`").append(co.getColName()).append("` ");
        sb.append(colType).append(" ").append(isNull).append(" ");
        if (StringUtils.isNotBlank(comment)) {
            sb.append(comment);
        }
        if (!flag) {
            sb.append(",");
        }

        return sb.toString();
    }

    private String typeMapping(String colType) {
        if (colType.contains("character varying")) {
            colType = colType.replace("character varying", "VARCHAR");
        } else if (colType.contains("numeric")) {
            colType = colType.replace("numeric", "NUMERIC");
        } else if (colType.contains("timestamp")) {
            colType = colType.replace(" without time zone", "");
        }
        return colType;
    }


}
